<?php
class Mbook extends CI_Model{
    public function __construct(){
        parent::__construct();
        $this->load->database();
    }

    public function getBook(){
        $this->db->select(' book.id, book.name, uname, image_id, page_title, page_keyword, 
                            page_description, description, type, author.name as author_name
                            ');
        $this->db->from('book');
        $this->db->join('author','book.author_id = author.id', 'inner');
        $this->db->limit(6, 0);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function getBooksByRank(){
        $this->db->select(' book.id, book.name, book.uname as book_uname, image_id, book.view,
                            category.uname as category_uname, 
                            image.filename as image_filename
                         ');
        $this->db->from('book');
        $this->db->join('book_rank'         , 'book.id = book_rank.book_id', 'inner');
        $this->db->join('category_book'     , 'book.id = category_book.book_id', 'inner');
        $this->db->join('category'          , 'category.id = category_book.category_id', 'inner');
        $this->db->join('image'             , 'book.image_id = image.id', 'left');
        $this->db->order_by('book_rank.rank', 'asc'); 
        $this->db->limit(10, 0);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function getBooksAll($number, $offset){
        $this->db->select(' DISTINCT book.id, book.name, book.uname as book_uname, image_id, book.use_volume,
                            category.uname as category_uname, category.name as category_name, book.view, book.status');
        $this->db->from('book');
        $this->db->join('category_book'     , 'book.id = category_book.book_id', 'inner');
        $this->db->join('category'          , 'category.id = category_book.category_id', 'inner');
        $this->db->join('chapter'           , 'book.id = chapter.book_id', 'inner');
        $this->db->order_by('book.update_date', 'DESC'); 
        $this->db->limit($number, $offset);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function getBookbyUname($uname){
        $this->db->select(' book.id, book.`name`, book.uname, image_id, page_title, page_keyword, 
                            page_description, description, book.type, author_id, view, use_volume,
                            image.filename as image_filename
                         ');
        $this->db->from('book');
        $this->db->join('image'             , 'book.image_id = image.id', 'left');
        $this->db->where("book.uname", $uname);
        $query = $this->db->get();
        return $query->row_array();
    }

    public function getBooksByCategoryUname($uname, $number, $offset){
        $this->db->select(' IFNULL(book.view, 0) as total_view,
                            category.name as category_name,category.uname as category_uname, 
                            book.page_title, book.page_keyword, book.page_description, 
                            book.uname as book_uname, book.name as book_name, 
                            author.name as author_name, book.description as book_description,
                            image.filename as image_filename
                         ');
        $this->db->from('category');
        $this->db->join('category_book' , 'category.id = category_book.category_id', 'inner');
        $this->db->join('book'          , 'book.id = category_book.book_id', 'left');
        $this->db->join('author'        , 'book.author_id = author.id', 'left');
        $this->db->join('image'         , 'book.image_id = image.id', 'left');
        $this->db->where('category.uname'   , $uname);
        $this->db->order_by('book.create_date', 'DESC'); 
        $this->db->limit($number, $offset);
        $query = $this->db->get();
        return $query->result_array();
    }
    
    public function getBookPopularBySumViewChapter(){
        $this->db->select(' IFNULL(book.view, 0) as total_view,
                            category.name as category_name,category.uname as category_uname, 
                            book.uname as book_uname, book.name as book_name
                          ', FALSE);
        $this->db->from('category');
        $this->db->join('category_book' , 'category.id = category_book.category_id', 'left');
        $this->db->join('book'          , 'book.id = category_book.book_id', 'left');
        $this->db->order_by('total_view', 'desc'); 
        $this->db->limit(10, 0);
        $query = $this->db->get();
        return $query->result_array();
    }
    
    public function getBooksByLikeName($name){
        $this->db->select(' IFNULL(book.view, 0) as total_view,
                            category.name as category_name,category.uname as category_uname, 
                            book.page_title, book.page_keyword, book.page_description, 
                            book.uname as book_uname, book.name as book_name, 
                            author.name as author_name, book.description as book_description,
                            image.filename as image_filename
                          ', FALSE);
        $this->db->from('category');
        $this->db->join('category_book' , 'category.id = category_book.category_id', 'right');
        $this->db->join('book'          , 'book.id = category_book.book_id', 'right');
        $this->db->join('author'        , 'book.author_id = author.id', 'inner');
        $this->db->join('image'         , 'book.image_id = image.id', 'left');
        $this->db->like('book.name'     , $name);
        $this->db->order_by('total_view', 'desc');
        $query = $this->db->get();
        return $query->result_array();
    }
    
    public function countBookByCategoryID($category_id){
        $this->db->select('count(book.id) as total');
        $this->db->from('book');
        $this->db->join('category_book' , 'book.id = category_book.book_id', 'inner');
        $this->db->where('category_book.category_id'   , $category_id);        
        $query = $this->db->get();
        return $query->row('total');
    }
    
    public function countBook(){
        $this->db->select('count(book.id) as total');
        $this->db->from('book');       
        $query = $this->db->get();
        return $query->row('total');
    }
    
    public function increaseView($book_id){
        $sql = "UPDATE book SET view = view + 1 WHERE id = ?";

        $this->db->query($sql, array($book_id));
    }
}
?>